﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class ScreenResultDal
    {
        public ILog log { get; set; }
        public DapperHelper dapperHelper { get; set; }

        #region 查询 
        /// <summary>
        /// 根据筛查ID查询筛查结果
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <returns></returns>
        public IEnumerable<ScreenResultView> GetScreenResultsByScreenId(string scid)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenResultView>>(a =>
            {
                string sql = @"select * from new_sc_resultBase r /**where**/";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("r.new_scid = @new_scid", new { new_scid = scid });

                return a.Query<ScreenResultView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });

        }

        /// <summary>
        /// 根据筛查ID,筛查客户ID查询筛查结果
        /// 原程序 ScreeningServiceImpl-> selectScResultByScidAndCustomerid(String scid, String customerid) 
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="customerid">筛查客户ID</param>
        /// <returns></returns>
        public IEnumerable<ScreenResultView> GetScreenResultsByScreenIdAndCustomerId(Guid scid,Guid customerid)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenResultView>>(a =>
            {
                string sql = $@"select new_sc_resultId, 
                                new_scid, new_categoryid,new_itemid,new_contentid, new_customerid, 
                                new_result, new_doctorid, CreatedOn,CreatedBy,ModifiedOn,ModifiedBy 
                                from new_sc_resultBase r with(nolock) where  r.new_scid='{scid}' and r.new_customerid='{customerid}'";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                return a.Query<ScreenResultView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据筛查ID,筛查客户ID查询筛查结果
        /// 原程序 ScreeningServiceImpl-> selectScResults(String scid, String customerid, List<String> contentids)
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="customerid">筛查客户ID</param>
        /// <param name="contentids">筛查内容ID</param>
        /// <returns></returns>
        public IEnumerable<ScreenResultView> GetScreenResultsByScreenIdAndCustomerIdAndContentIds(Guid scid, Guid customerid, List<string> contentids)
        {
            DynamicParameters parameters = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenResultView>>(a =>
            {
                string sql = $@"select new_sc_resultId, 
                                new_scid, new_categoryid,new_itemid,new_contentid, new_customerid, 
                                new_result, new_doctorid, CreatedOn,CreatedBy,ModifiedOn,ModifiedBy 
                                from new_sc_resultBase r with(nolock) /**where**/ ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where("r.new_scid=@new_scid and r.new_customerid=@new_customerid and r.new_contentid in @new_contentid");

                parameters.Add("@new_scid", scid);
                parameters.Add("@new_customerid", customerid);
                parameters.Add("@new_contentid", contentids);

                log.Info(string.Format("ScreenDal 对像中,GetAccountCustomerInfoByTeamIdAndCreatedOnAndPhone 方法 ,SQL：'{0}' 参数：'{1}'", sqlTemplate.RawSql,parameters.ParametersToString(sqlTemplate.RawSql)));

                return a.Query<ScreenResultView>(sqlTemplate.RawSql, parameters);
            });
        }

        /// <summary>
        /// 根据筛查ID,筛查客户ID查询筛查结果
        /// 原程序 ScreeningServiceImpl-> selectScResults(String scid, String customerid, List<String> contentids)
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="customerid">筛查客户ID</param>
        /// <param name="contentids">筛查内容ID</param>
        /// <returns></returns>
        public IEnumerable<ScreenResultView> GetScreenResultsByScreenIdAndCustomerIdAndCategoryId(Guid scid, Guid customerid, string new_categoryid)
        {
            DynamicParameters parameters = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenResultView>>(a =>
            {
                string sql = $@"select new_sc_resultId, 
                                new_scid, new_categoryid,new_itemid,new_contentid, new_customerid, 
                                new_result, new_doctorid, CreatedOn,CreatedBy,ModifiedOn,ModifiedBy 
                                from new_sc_resultBase r with(nolock) /**where**/ ";
                
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where("r.new_scid=@new_scid and r.new_customerid=@new_customerid and r.new_categoryid = @new_categoryid");

                parameters.Add("@new_scid", scid);
                parameters.Add("@new_customerid", customerid);
                parameters.Add("@new_categoryid", new_categoryid);

                log.Info(string.Format("ScreenDal 对像中,GetAccountCustomerInfoByTeamIdAndCreatedOnAndPhone 方法 ,SQL：'{0}' 参数：'{1}'", sqlTemplate.RawSql, parameters.ParametersToString(sqlTemplate.RawSql)));

                return a.Query<ScreenResultView>(sqlTemplate.RawSql, parameters);
            });
        }

        /// <summary>
        /// 根据筛查ID,筛查客户ID查询筛查结果
        /// 原程序 ScreeningServiceImpl-> selectScResults(String scid, String customerid, List<String> contentids)
        /// </summary>
        /// <param name="scid">筛查ID</param>
        /// <param name="customerid">筛查客户ID</param>
        /// <param name="contentids">筛查内容ID</param>
        /// <returns></returns>
        public IEnumerable<string> GetSelectedByScreenIdAndCustomerId(Guid scid, Guid customerid)
        {
            DynamicParameters parameters = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<string>>(a =>
            {
                string sql = $@"select new_categoryid
                                from new_sc_resultBase r with(nolock) /**where**/ ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where("r.new_scid=@new_scid and r.new_customerid=@new_customerid ");

                parameters.Add("@new_scid", scid);
                parameters.Add("@new_customerid", customerid);

                log.Info(string.Format("ScreenDal 对像中,GetSelectedByScreenIdAndCustomerId 方法 ,SQL：'{0}' 参数：'{1}'", sqlTemplate.RawSql, parameters.ParametersToString(sqlTemplate.RawSql)));

                return a.Query<string>(sqlTemplate.RawSql, parameters);
            });
        }

        #endregion


        /// <summary>
        /// 根据潜客Id与筛查Id获取当前客户的筛查结果
        /// </summary>
        /// <param name="leadId"></param>
        /// <param name="screenid"></param>
        /// <returns></returns>
        public IEnumerable<ScreenResultView> GetScreenResultByLeadIdAndScreenId(Guid leadId, Guid screenid)
        {
            if (leadId != Guid.Empty && screenid != Guid.Empty)
            {
                return new DapperHelper().ExecueWithoutTransaction(select =>
                {
                    string strsql = @"select new_customerid,
                                      result.new_itemid,
                                      isnull((select new_itemname from new_t_sc_item as i where i.new_itemid=result.new_itemid),'无数据') as new_itemname,
                                      result.new_contentid,
                                      (select top 1 c.new_contentname from   dbo.AIER_T_SC_CONTENT as c where c.new_contentid=result.new_contentid) as new_contentname, 
                                      isnull((select new_paravalue from new_t_sys_para as p where p.new_paracode=result.new_result),'无数据') as new_paravalue
                                      from new_sc_result as result  
                                      /**where**/";
                    SqlBuilder sqlBuilder = new SqlBuilder();
                    var sqlTemplate = sqlBuilder.AddTemplate(strsql);
                    sqlBuilder.Where("result.new_customerid = @leadId", new { leadId = leadId });
                    sqlBuilder.Where("result.new_scid = @screenid", new { screenid = screenid });
                    return select.Query<ScreenResultView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
                });
            }
            return null;
        }
        /// <summary>
        /// 删除筛查结果
        /// </summary>
        /// <param name="screenId">筛查ID</param>
        /// <param name="leadId">潜客ID</param>
        /// <returns></returns>
        public bool DeleteScreenResultByScreenIdAndLeadId(Guid screenId,Guid leadId)
        {
            return new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string sql = $@" delete new_sc_resultBase   where  new_scid='{screenId}' and new_customerid='{leadId}' ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Execute(sqlTemplate.RawSql) >= 0 ? true : false;
            });

        }

        /// <summary>
        /// 删除筛查结果
        /// </summary>
        /// <param name="screenId">筛查ID</param>
        /// <param name="leadId">潜客ID</param>
        /// <returns></returns>
        public bool DeleteScreenResultByContentId(string categoryidid, string itemid, string contentid, string customerid)
        {
            return new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string sql = $@" delete new_sc_resultBase   
                                        where  new_categoryid='{categoryidid}' 
                                               and new_itemid='{itemid}' 
                                               and new_contentid='{contentid}' 
                                               and  new_customerid='{customerid}'";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Execute(sqlTemplate.RawSql) >= 0 ? true : false;
            });

        }



        /// <summary>
        /// 删除筛查结果
        /// </summary>
        /// <param name="screenId">筛查ID</param>
        /// <param name="leadId">潜客ID</param>
        /// <returns></returns>
        public bool DeleteScreenResultByContentId(string categoryidid, string itemid, string contentid, string customerid,string scid)
        {
            return new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string sql = $@" delete new_sc_resultBase   
                                        where  new_categoryid='{categoryidid}' 
                                               and new_itemid='{itemid}' 
                                               and new_contentid='{contentid}' 
                                               and new_customerid='{customerid}'
                                               and new_scid='{scid}'";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Execute(sqlTemplate.RawSql) >= 0 ? true : false;
            });

        }

        /// <summary>
        /// 删除筛查结果
        /// </summary>
        /// <param name="screenId">筛查ID</param>
        /// <param name="leadId">潜客ID</param>
        /// <returns></returns>
        public bool DeleteScreenResultByGuid(string ids)
        {
            return new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string sql = $@" delete new_sc_resultBase   
                                        where  new_sc_resultid in ({ids})";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Execute(sqlTemplate.RawSql) >= 0 ? true : false;
            });

        }

        public bool DeleteScreenResultByGuid(string[] ids)
        {
            return new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string sql = $@" delete new_sc_resultBase   /**where**/ ";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where(" new_sc_resultid in @ids ", new { ids = ids });
                return a.Execute(sqlTemplate.RawSql,sqlTemplate.Parameters) >= 0 ? true : false;
            });

        }
    }
}
